﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using StudentProject.Model;
using System.Data;
using System.Data.SqlClient;

namespace StudentProject.DAL
{
    public class SubjectServiceDal
    {
        //显示数据在DGVSubject中
        public List<Subject> GetList()
        {
            //sql语句
            string sql = "select * from Subject";
            //使用SqlHelper进行查询，
            DataTable dt = SqlHelper.ExecuteDataTable(sql);
            List<Subject> list = new List<Subject>();
            foreach (DataRow item in dt.Rows)
            {
                list.Add(new Subject()
                {
                    ID = item["ID"].ToString(),
                    Name = item["Name"].ToString()
                });
            }
            return list;
        }

        //修改
        public int UpdateSubjectInfo(Subject subject)
        {
            string sql = "update Subject set Name=@Name where Id=@pid";
            SqlParameter[] ps =
            {
                new SqlParameter("@pid",subject.ID),
                new SqlParameter("@Name",subject.Name)
            };
            return SqlHelper.ExecuteNonQuery(sql, ps);
        }

        //删除
        public int RemoveSubject(string pid)
        {
            //构造删除的sql语句
            string sql = "delete from Subject where ID=@id";
            //根据语句构造参数
            SqlParameter ps = new SqlParameter("@id", pid);
            //执行操作
            return SqlHelper.ExecuteNonQuery(sql, ps);
        }

        //查询数据显示在修改窗口中的文本框中
        public Subject LoadSubjectInfo(string pid)
        {
            string sql = "select * from Subject where ID=@pid";
            SqlParameter ps = new SqlParameter("@pid", pid);
            Subject subject = null;
            SqlDataReader dr = SqlHelper.ExecuteReader(sql, ps);
            if (dr.HasRows)//是否有行
            {
                while (dr.Read())
                {
                    subject = new Subject();
                    subject.ID = dr.GetString(0);
                    subject.Name = dr.GetString(1);

                }
            }
            return subject;
        }

        //添加数据
        public int AddSubjectInfo(Subject subject)
        {
            //添加的SQL语句
            string sql = "insert into Subject(ID,Name) values(@ID,@Name)";
            //SQL语句的参数
            SqlParameter[] ps =
            {
                new SqlParameter("@ID",subject.ID),
                new SqlParameter("@Name",subject.Name)
            };
            //返回操作
            return SqlHelper.ExecuteNonQuery(sql, ps);
        }


    }
}
